package edu.yuhf.jdbcpart;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import edu.yuhf.domain.User;

public class UserTableOperation {
	// 查询全部
		public List<User> queryAll() {
			String sql = "select * from users";
			Connection connection = DBConnection.getConnection();
			List<User> list = new ArrayList<>();
			try {
				PreparedStatement psmt = connection.prepareStatement(sql);
				ResultSet rs = psmt.executeQuery();
				while (rs.next()) {
					User user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
							LocalDateTime.now());
					list.add(user);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return list;
		}

		// 根据id查找
		public User queryById(int id) {
			String sql = "select * from users where id=?";
			Connection connection = DBConnection.getConnection();
			User user = null;
			try {
				PreparedStatement psmt = connection.prepareStatement(sql);
				psmt.setInt(1, id);
				ResultSet rs = psmt.executeQuery();
				if (rs.next()) {
					user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), LocalDateTime.now());
				}
			} catch (SQLException e) {
				System.out.println(
						UserTableOperation.class.getName() + ":quer By Id method error,message is" + e.getMessage());

			}
			return user;
		}

		// 根据name查找
		public User queryByName(String name) {
			String sql = "select * from users where name= ?";
			Connection connection = DBConnection.getConnection();
			User user1 = null;
			try {
				PreparedStatement psmt = connection.prepareStatement(sql);
				psmt.setString(1, name);
				ResultSet rs = psmt.executeQuery();
				if (rs.next()) {
					user1 = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), LocalDateTime.now());
				}
			} catch (SQLException e) {
				System.out.println(
						UserTableOperation.class.getName() + ":quer By Name method error,message is" + e.getMessage());
			}
			return user1;
		}

		// 添加
		public int addUser(User user) {
			int rowNum = 0;
			String sql = "insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
			Connection connection = DBConnection.getConnection();
			try {
				PreparedStatement psmt = connection.prepareStatement(sql);
				psmt.setString(1, user.getName());
				psmt.setString(2, user.getPassword());
				psmt.setString(3, user.getSex());
				rowNum = psmt.executeUpdate();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println(
						UserTableOperation.class.getName() + ":add user method error, message is" + e.getMessage());
			}

			return rowNum;
		}

		// 删除
		public int deleteUser(int id) {
			int rowNum1 = 0;
			String sql = "delete from users where id= ?";
			Connection connection = DBConnection.getConnection();

			try {
				PreparedStatement psmt = connection.prepareStatement(sql);
				psmt.setInt(1, id);
				rowNum1 = psmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println(
						UserTableOperation.class.getName() + ":delete user method error, message is" + e.getMessage());
			}
			return rowNum1;
		}

		// 修改
		public int upDate(User user) {
			int rowNum2 = 0;
			String sql = "update users set name = ?,password = ?,sex = ? where id = ?";
			Connection connection = DBConnection.getConnection();

			try {
				PreparedStatement psmt = connection.prepareStatement(sql);
				psmt.setString(1, user.getName());
				psmt.setString(2, user.getPassword());
				psmt.setString(3, user.getSex());
				psmt.setInt(4, user.getId());
				rowNum2 = psmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println(
						UserTableOperation.class.getName() + ":update user method error, message is" + e.getMessage());
			}
			return rowNum2;
		}
}